#ifndef __M_DB_H__
#define __M_DB_H__
#include "util.hpp"
#include <mutex>
#include <cassert>
class user_table {  
private:
    MYSQL* _mysql;       // mysql句柄
    std::mutex _mutex;   // 保护数据库访问操作

public:
    user_table(const std::string& host, const std::string& username, 
               const std::string& password, const std::string& dbname, 
               uint16_t port = 3306) {
        
        _mysql = mysql_util::mysql_create(host, username, password, dbname, port);
        assert(_mysql != nullptr);  
    }

    ~user_table() {
        if (_mysql != nullptr) {
            mysql_util::mysql_destroy(_mysql);
            _mysql = nullptr;
        }
    }

    // 注册增加用户
  bool insert(Json::Value& user) {
#define INSERT_USER "insert into user values(null,'%s',password('%s'),1000,0,0);"
    char sql[4096] = {0};

    // 1. 检查字段
    if (!user.isMember("username") || !user.isMember("password")) {
        ELOG("insert: missing username or password\n");
        return false;
    }

    // 2. 防SQL注入+拼接SQL
    char escaped_user[2048] = {0};
    char escaped_pwd[2048] = {0};
    mysql_escape_string(escaped_user, user["username"].asCString(), strlen(user["username"].asCString()));
    mysql_escape_string(escaped_pwd, user["password"].asCString(), strlen(user["password"].asCString()));
    int sql_len = snprintf(sql, sizeof(sql)-1, INSERT_USER, escaped_user, escaped_pwd);
    if (sql_len < 0 || sql_len >= (int)sizeof(sql)-1) {
        ELOG("insert: SQL buffer overflow\n");
        return false;
    }

    // 3. 加锁+清理残留
    std::unique_lock<std::mutex> lock(_mutex);
    try {
        mysql_free_result(mysql_store_result(_mysql));  // 清理残留
        bool ret = mysql_util::mysql_exec(_mysql, sql);
        if (!ret) {  
            ELOG("insert: failed: %s\n", mysql_error(_mysql));
            return false;
        }
        // 检查影响行数（确保插入成功）
        if (mysql_affected_rows(_mysql) != 1) {
            ELOG("insert: no row inserted\n");
            return false;
        }
    } catch (const std::exception& e) {
        ELOG("insert: exception: %s\n", e.what());
        return false;
    }

    return true;
}

    // 登录用户
   	bool login(Json::Value &user) {
#define LOGIN_USER "select id,score,total_count,win_count from user where username='%s' and password=password('%s');"
    char sql[4096] = {0};
    const char* username = nullptr;

    // 1. 提前检查必要字段，避免加锁后做无效操作
    if (!user.isMember("username") || !user.isMember("password")) {
        ELOG("login: missing username or password\n");
        return false;
    }
    username = user["username"].asCString();

    // 2. 安全拼接SQL（防缓冲区溢出）
    int sql_len = snprintf(sql, sizeof(sql)-1, LOGIN_USER, 
                          username, user["password"].asCString());
    if (sql_len < 0 || sql_len >= (int)sizeof(sql)-1) {
        ELOG("login: SQL buffer overflow for user %s\n", username);
        return false;
    }

    // 3. 加锁：覆盖整个数据库操作流程（从执行SQL到释放结果集）
    std::unique_lock<std::mutex> lock(_mutex);
    MYSQL_RES* res = nullptr;
    try {
        // 4. 清理残留结果集（关键：避免上一次操作的残留阻塞）
        mysql_free_result(mysql_store_result(_mysql));

        // 5. 执行SQL
        bool ret = mysql_util::mysql_exec(_mysql, sql);
        if (!ret) {  
            ELOG("login: SQL execute failed for user %s: %s\n", 
                 username, mysql_error(_mysql));
            return false;
        }

        // 6. 获取结果集
        res = mysql_store_result(_mysql);
        if (res == nullptr) {
            ELOG("login: no result for user %s: %s\n", 
                 username, mysql_error(_mysql));
            return false;
        }

        // 7. 检查结果行数
        int row_num = mysql_num_rows(res);
        if (row_num != 1) {  
            ELOG("login: user %s not found or duplicate (rows: %d)\n", 
                 username, row_num);
            return false;
        }

        // 8. 提取行数据（在锁内执行，避免跨线程污染）
        MYSQL_ROW row = mysql_fetch_row(res);
        if (row == NULL) {
            ELOG("login: fetch row failed for user %s\n", username);
            return false;
        }

        // 9. 数据转换（锁内执行，确保数据完整性）
        user["id"] = (Json::UInt64)std::stoull(row[0]);
        user["score"] = (Json::UInt64)std::stoull(row[1]);
        user["total_count"] = std::stoi(row[2]);
        user["win_count"] = std::stoi(row[3]);

    } catch (const std::exception& e) {
        // 10. 异常场景下释放结果集（避免泄漏）
        ELOG("login: exception for user %s: %s\n", username, e.what());
        if (res != nullptr) {
            mysql_free_result(res);
        }
        return false;
    }

    // 11. 正常场景下释放结果集（锁内释放，避免跨线程占用）
    if (res != nullptr) {
        mysql_free_result(res);
    }

    DLOG("login: user %s success\n", username);
    return true;
}
    // 通过用户名获取信息
    bool select_by_name(const std::string& name, Json::Value& user) {
#define USER_BY_NAME "select id,score,total_count,win_count from user where username='%s';"
    char sql[4096] = {0};

    // 1. 防SQL注入（转义特殊字符，避免语法错误导致阻塞）
    char escaped_name[2048] = {0};
    mysql_escape_string(escaped_name, name.c_str(), name.size());
    int sql_len = snprintf(sql, sizeof(sql)-1, USER_BY_NAME, escaped_name);
    if (sql_len < 0 || sql_len >= (int)sizeof(sql)-1) {
        ELOG("select_by_name: SQL buffer overflow for name %s\n", name.c_str());
        return false;
    }

    // 2. 加锁覆盖全程
    std::unique_lock<std::mutex> lock(_mutex);
    MYSQL_RES* res = nullptr;
    try {
        // 3. 清理残留结果集
        mysql_free_result(mysql_store_result(_mysql));

        // 4. 执行SQL
        bool ret = mysql_util::mysql_exec(_mysql, sql);
        if (!ret) {
            ELOG("select_by_name: SQL failed for %s: %s\n", name.c_str(), mysql_error(_mysql));
            return false;
        }

        // 5. 获取结果集
        res = mysql_store_result(_mysql);
        if (res == nullptr) {
            ELOG("select_by_name: no result for %s: %s\n", name.c_str(), mysql_error(_mysql));
            return false;
        }

        // 6. 检查行数
        int row_num = mysql_num_rows(res);
        if (row_num != 1) {  
            ELOG("select_by_name: user %s not found (rows: %d)\n", name.c_str(), row_num);
            return false;
        }

        // 7. 提取数据
        MYSQL_ROW row = mysql_fetch_row(res);
        if (row == nullptr) {
            ELOG("select_by_name: fetch row failed for %s\n", name.c_str());
            return false;
        }

        // 8. 数据转换
        user["id"] = (Json::UInt64)std::stoull(row[0]);
        user["username"] = name;
        user["score"] = (Json::UInt64)std::stoull(row[1]);
        user["total_count"] = std::stoi(row[2]);
        user["win_count"] = std::stoi(row[3]);

    } catch (const std::exception& e) {
        ELOG("select_by_name: exception for %s: %s\n", name.c_str(), e.what());
        if (res != nullptr) {
            mysql_free_result(res);
        }
        return false;
    }

    // 9. 释放结果集
    if (res != nullptr) {
        mysql_free_result(res);
    }

    return true;
}
    // 通过id获取信息
  bool select_by_id(uint64_t id, Json::Value& user) {
#define USER_BY_ID "select username,score,total_count,win_count from user where id=%llu;"  
    char sql[4096] = {0};

    // 1. 拼接SQL
    int sql_len = snprintf(sql, sizeof(sql)-1, USER_BY_ID, id);
    if (sql_len < 0 || sql_len >= (int)sizeof(sql)-1) {
        ELOG("select_by_id: SQL overflow for id %llu\n", id);
        return false;
    }

    // 2. 加锁覆盖全程
    std::unique_lock<std::mutex> lock(_mutex);
    MYSQL_RES* res = nullptr;
    try {
        // 3. 清理残留结果集
        mysql_free_result(mysql_store_result(_mysql));

        // 4. 执行SQL
        bool ret = mysql_util::mysql_exec(_mysql, sql);
        if (!ret) {
            ELOG("select_by_id: SQL failed for %llu: %s\n", id, mysql_error(_mysql));
            return false;
        }

        // 5. 获取结果集
        res = mysql_store_result(_mysql);
        if (res == nullptr) {
            ELOG("select_by_id: no result for %llu: %s\n", id, mysql_error(_mysql));
            return false;
        }

        // 6. 检查行数
        int row_num = mysql_num_rows(res);
        if (row_num != 1) {  
            ELOG("select_by_id: user %llu not found (rows: %d)\n", id, row_num);
            return false;
        }

        // 7. 提取数据
        MYSQL_ROW row = mysql_fetch_row(res);
        if (row == nullptr) {
            ELOG("select_by_id: fetch row failed for %llu\n", id);
            return false;
        }

        // 8. 数据转换
        user["id"] = (Json::UInt64)id;
        user["username"] = row[0];
        user["score"] = (Json::UInt64)std::stoull(row[1]);  
        user["total_count"] = std::stoi(row[2]);
        user["win_count"] = std::stoi(row[3]);

    } catch (const std::exception& e) {
        ELOG("select_by_id: exception for %llu: %s\n", id, e.what());
        if (res != nullptr) {
            mysql_free_result(res);
        }
        return false;
    }

    // 9. 释放结果集
    if (res != nullptr) {
        mysql_free_result(res);
    }

    return true;
}
		bool win(uint64_t id) {
#define USER_WIN "update user set score=score+30, total_count=total_count+1, win_count=win_count+1 where id=%llu;"
    char sql[4096] = {0};

    int sql_len = snprintf(sql, sizeof(sql)-1, USER_WIN, id);
    if (sql_len < 0 || sql_len >= (int)sizeof(sql)-1) {
        ELOG("win: SQL overflow for id %llu\n", id);
        return false;
    }

    std::unique_lock<std::mutex> lock(_mutex);
    try {
        mysql_free_result(mysql_store_result(_mysql));  // 清理残留
        bool ret = mysql_util::mysql_exec(_mysql, sql);
        if (!ret) {  
            ELOG("win: failed for %llu: %s\n", id, mysql_error(_mysql));
            return false;
        }
        if (mysql_affected_rows(_mysql) != 1) {
            ELOG("win: no row updated for %llu\n", id);
            return false;
        }
    } catch (const std::exception& e) {
        ELOG("win: exception for %llu: %s\n", id, e.what());
        return false;
    }

    DLOG("win: success for %llu\n", id);
    return true;
}

bool lose(uint64_t id) {
#define USER_LOSE "update user set score=score-30, total_count=total_count+1 where id=%llu;"
    char sql[4096] = {0};

    int sql_len = snprintf(sql, sizeof(sql)-1, USER_LOSE, id);
    if (sql_len < 0 || sql_len >= (int)sizeof(sql)-1) {
        ELOG("lose: SQL overflow for id %llu\n", id);
        return false;
    }

    std::unique_lock<std::mutex> lock(_mutex);
    try {
        mysql_free_result(mysql_store_result(_mysql));  // 清理残留
        bool ret = mysql_util::mysql_exec(_mysql, sql);
        if (!ret) {  
            ELOG("lose: failed for %llu: %s\n", id, mysql_error(_mysql));
            return false;
        }
        if (mysql_affected_rows(_mysql) != 1) {
            ELOG("lose: no row updated for %llu\n", id);
            return false;
        }
    } catch (const std::exception& e) {
        ELOG("lose: exception for %llu: %s\n", id, e.what());
        return false;
    }

    DLOG("lose: success for %llu\n", id);
    return true;
}

};
#endif
